package com.hotelManager.www.dao;

import com.hotelManager.www.po.Room;
import com.hotelManager.www.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

public class RoomDao {
    private static final Random random = new Random();

    /**
     * 创建房间
     */
    public boolean create(Connection conn, Room room) throws SQLException {
        PreparedStatement ps = null;
        try {
            String sql = "INSERT INTO room (room_type_id, status, room_code) VALUES (?, ?, ?)";
            ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setInt(1, room.getRoomTypeId());
            ps.setString(2, room.getStatus());
            ps.setString(3, room.getRoomCode());
            int affectedRows = ps.executeUpdate();
            if (affectedRows > 0) {
                try (ResultSet generatedKeys = ps.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        room.setId(generatedKeys.getInt(1));
                        return true;
                    }
                }
            }
            return false;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) ps.close();
        }
    }

    /**
     * 根据ID获取房间
     */
    public Room findById(Integer id) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "SELECT * FROM room WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                return extractRoomFromResultSet(rs);
            }
            return null;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    /**
     * 根据房间类型ID获取房间列表
     */
    public List<Room> findByRoomTypeId(Connection conn, Integer roomTypeId) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "SELECT * FROM room WHERE room_type_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, roomTypeId);
            rs = ps.executeQuery();
            List<Room> rooms = new ArrayList<>();
            while (rs.next()) {
                rooms.add(extractRoomFromResultSet(rs));
            }
            return rooms;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    /**
     * 更新房间状态
     */
    public boolean update(Connection conn, Room room) throws SQLException {
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "UPDATE room SET  room_type_id = ?, status = ?, room_code = ? WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(2, room.getRoomTypeId());
            ps.setString(5, room.getStatus());
            ps.setString(1, room.getRoomCode());
            ps.setInt(7, room.getId());
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    /**
     * 删除房间
     */
    public boolean delete(Integer id) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "DELETE FROM room WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    /**
     * 删除指定房型的所有房间
     */
    public boolean deleteByRoomTypeId(Integer roomTypeId) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "DELETE FROM room WHERE room_type_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, roomTypeId);
            return ps.executeUpdate() > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    /**
     * 将ResultSet映射到Room对象
     */
    private Room extractRoomFromResultSet(ResultSet rs) throws SQLException {
        Room room = new Room();
        room.setId(rs.getInt("id"));
        room.setRoomTypeId(rs.getInt("room_type_id"));
        room.setStatus(rs.getString("status"));
        room.setRoomCode(rs.getString("room_code"));
        return room;
    }

    /**
     * 根据房间号查找房间
     */
    public Room findByRoomCode(Connection conn, String roomCode) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM room WHERE room_code = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, roomCode);
            rs = ps.executeQuery();
            if (rs.next()) {
                return extractRoomFromResultSet(rs);
            }
            return null;
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
        }
    }

    /**
     * 生成唯一的房型号
     */
    public String generateUniqueRoomCode(Connection conn, Integer roomTypeId) throws SQLException {
        String roomCode;
        int attempts = 0;
        final int MAX_ATTEMPTS = 10;

        do {
            if (attempts >= MAX_ATTEMPTS) {
                throw new SQLException("无法生成唯一的房型号，请稍后再试");
            }
            
            roomCode = String.format("%03d", random.nextInt(900) + 100);
            attempts++;
        } while (isRoomCodeExists(conn, roomTypeId, roomCode));

        return roomCode;
    }

    /**
     * 检查房型号是否已存在
     */
    private boolean isRoomCodeExists(Connection conn, Integer roomTypeId, String roomCode) throws SQLException {
        String sql = "SELECT COUNT(*) FROM room WHERE room_type_id = ? AND room_code = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, roomTypeId);
            pstmt.setString(2, roomCode);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }

    /**
     * 通过房间号更新房间状态
     */
    public boolean updateStatusByRoomCode(Connection conn, String roomCode, String status) throws SQLException {
        PreparedStatement ps = null;
        try {
            String sql = "UPDATE room SET status = ? WHERE room_code = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, status);
            ps.setString(2, roomCode);
            return ps.executeUpdate() > 0;
        } finally {
            if (ps != null) ps.close();
        }
    }

    /**
     * 获取所有房间
     */
    public List<Room> findAll() throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "SELECT * FROM room";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            List<Room> rooms = new ArrayList<>();
            while (rs.next()) {
                rooms.add(extractRoomFromResultSet(rs));
            }
            return rooms;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        }
    }

    /**
     * 根据店铺ID获取房间列表
     */
    public List<Room> findByShopId(Connection conn, int shopId) throws SQLException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT r.* FROM room r " +
                        "JOIN room_type rt ON r.room_type_id = rt.id " +
                        "WHERE rt.shop_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, shopId);
            rs = ps.executeQuery();
            List<Room> rooms = new ArrayList<>();
            while (rs.next()) {
                rooms.add(extractRoomFromResultSet(rs));
            }
            return rooms;
        } finally {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
        }
    }

    /**
     * 根据店铺ID和房型名称获取房间列表
     */
    public List<Room> findByType(Connection conn, int shopId, String roomType) throws SQLException {
        String sql = "SELECT r.* FROM room r " +
                     "JOIN room_type rt ON r.room_type_id = rt.id " +
                     "WHERE rt.shop_id = ? AND rt.type_name = ?";
        List<Room> rooms = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, shopId);
            ps.setString(2, roomType);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    rooms.add(extractRoomFromResultSet(rs));
                }
            }
        }
        return rooms;
    }
}